

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=auto>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/fluid.png">
  <link rel="icon" href="/img/fluid.png">
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=5.0, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="author" content="XiaoMaDing">
  <meta name="keywords" content="">
  
    <meta name="description" content="前言Excel批量导入是软件开发中常见的需求之一。在许多业务场景下，用户需要将大量数据从Excel中，导入到系统中，以提高工作效率。 听起来，Excel导入，没什么技术含量。大家会说直接用阿里的EasyExcel，解析Excel文件，插入数据库，就完事了嘛。技术实现的大体流程确实如此。 不过本文的侧重点并非是使用什么三方库，来实现Excel导入的功能，而是在Excel导入过程中，有哪些技术点，以及">
<meta property="og:type" content="article">
<meta property="og:title" content="项目实战：Excel批量导入中涉及的技术点">
<meta property="og:url" content="http://example.com/2023/05/01/%E9%A1%B9%E7%9B%AE%E5%AE%9E%E6%88%98%EF%BC%9AExcel%E6%89%B9%E9%87%8F%E5%AF%BC%E5%85%A5%E4%B8%AD%E6%B6%89%E5%8F%8A%E7%9A%84%E6%8A%80%E6%9C%AF%E7%82%B9/index.html">
<meta property="og:site_name" content="剑出">
<meta property="og:description" content="前言Excel批量导入是软件开发中常见的需求之一。在许多业务场景下，用户需要将大量数据从Excel中，导入到系统中，以提高工作效率。 听起来，Excel导入，没什么技术含量。大家会说直接用阿里的EasyExcel，解析Excel文件，插入数据库，就完事了嘛。技术实现的大体流程确实如此。 不过本文的侧重点并非是使用什么三方库，来实现Excel导入的功能，而是在Excel导入过程中，有哪些技术点，以及">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://example.com/index_img/excel.png">
<meta property="article:published_time" content="2023-05-01T04:24:53.000Z">
<meta property="article:modified_time" content="2023-12-26T03:48:16.454Z">
<meta property="article:author" content="XiaoMaDing">
<meta property="article:tag" content="Excel">
<meta property="article:tag" content="反射">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="http://example.com/index_img/excel.png">
  
  
    <meta name="referrer" content="no-referrer-when-downgrade">
  
  
  <title>项目实战：Excel批量导入中涉及的技术点 - 剑出</title>

  <link  rel="stylesheet" href="https://lib.baomitu.com/twitter-bootstrap/4.6.1/css/bootstrap.min.css" />



  <link  rel="stylesheet" href="https://lib.baomitu.com/github-markdown-css/4.0.0/github-markdown.min.css" />

  <link  rel="stylesheet" href="https://lib.baomitu.com/hint.css/2.7.0/hint.min.css" />

  <link  rel="stylesheet" href="https://lib.baomitu.com/prism/1.29.0/plugins/line-numbers/prism-line-numbers.min.css" />

  <link  rel="stylesheet" href="https://lib.baomitu.com/fancybox/3.5.7/jquery.fancybox.min.css" />



<!-- 主题依赖的图标库，不要自行修改 -->
<!-- Do not modify the link that theme dependent icons -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_hj8rtnfg7um.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_lbnruvf0jn.css">


<link  rel="stylesheet" href="/css/main.css" />


  <link id="highlight-css" rel="stylesheet" href="/css/highlight.css" />
  
    <link id="highlight-css-dark" rel="stylesheet" href="/css/highlight-dark.css" />
  




  <script id="fluid-configs">
    var Fluid = window.Fluid || {};
    Fluid.ctx = Object.assign({}, Fluid.ctx)
    var CONFIG = {"hostname":"example.com","root":"/","version":"1.9.5-a","typing":{"enable":true,"typeSpeed":40,"cursorChar":"_","loop":false,"scope":[]},"anchorjs":{"enable":true,"element":"h1,h2,h3,h4,h5,h6","placement":"left","visible":"hover","icon":""},"progressbar":{"enable":true,"height_px":3,"color":"#29d","options":{"showSpinner":false,"trickleSpeed":100}},"code_language":{"enable":true,"default":"TEXT"},"copy_btn":true,"image_caption":{"enable":true},"image_zoom":{"enable":true,"img_url_replace":["",""]},"toc":{"enable":true,"placement":"left","headingSelector":"h1,h2,h3,h4,h5,h6","collapseDepth":4},"lazyload":{"enable":true,"loading_img":"/img/loading.gif","onlypost":false,"offset_factor":2},"web_analytics":{"enable":true,"follow_dnt":false,"baidu":"e0dcc0c9ab8f117bb83579322e04efb8","google":null,"gtag":null,"tencent":{"sid":null,"cid":null},"woyaola":null,"cnzz":null,"leancloud":{"app_id":null,"app_key":null,"server_url":null,"path":"window.location.pathname","ignore_local":false}},"search_path":"/local-search.xml","include_content_in_search":true};

    if (CONFIG.web_analytics.follow_dnt) {
      var dntVal = navigator.doNotTrack || window.doNotTrack || navigator.msDoNotTrack;
      Fluid.ctx.dnt = dntVal && (dntVal.startsWith('1') || dntVal.startsWith('yes') || dntVal.startsWith('on'));
    }
  </script>
  <script  src="/js/utils.js" ></script>
  <script  src="/js/color-schema.js" ></script>
  

  
    <!-- Baidu Analytics -->
    <script async>
      if (!Fluid.ctx.dnt) {
        var _hmt = _hmt || [];
        (function() {
          var hm = document.createElement("script");
          hm.src = "https://hm.baidu.com/hm.js?e0dcc0c9ab8f117bb83579322e04efb8";
          var s = document.getElementsByTagName("script")[0];
          s.parentNode.insertBefore(hm, s);
        })();
      }
    </script>
  

  

  

  

  

  



  
<meta name="generator" content="Hexo 6.3.0"></head>


<body>
  

  <header>
    

<div class="header-inner" style="height: 60vh;">
  <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand" href="/">
      <strong>剑出</strong>
    </a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                <span>首页</span>
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                <span>归档</span>
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                <span>分类</span>
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                <span>标签</span>
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                <span>关于</span>
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" target="_self" href="javascript:;" data-toggle="modal" data-target="#modalSearch" aria-label="Search">
              <i class="iconfont icon-search"></i>
            </a>
          </li>
          
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" target="_self" href="javascript:;" aria-label="Color Toggle">
              <i class="iconfont icon-dark" id="color-toggle-icon"></i>
            </a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

  

<div id="banner" class="banner" parallax=true
     style="background: url('/img/yuzhou.gif') no-repeat center center; background-size: cover;">
  <div class="full-bg-img">
    <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
      <div class="banner-text text-center fade-in-up">
        <div class="h2">
          
            <span id="subtitle" data-typed-text="项目实战：Excel批量导入中涉及的技术点"></span>
          
        </div>

        
          
  <div class="mt-3">
    
    
      <span class="post-meta">
        <i class="iconfont icon-date-fill" aria-hidden="true"></i>
        <time datetime="2023-05-01 12:24" pubdate>
          2023年5月1日 中午
        </time>
      </span>
    
  </div>

  <div class="mt-1">
    
      <span class="post-meta mr-2">
        <i class="iconfont icon-chart"></i>
        
          7k 字
        
      </span>
    

    
      <span class="post-meta mr-2">
        <i class="iconfont icon-clock-fill"></i>
        
        
        
          59 分钟
        
      </span>
    

    
    
      
        <span id="busuanzi_container_page_pv" style="display: none">
          <i class="iconfont icon-eye" aria-hidden="true"></i>
          <span id="busuanzi_value_page_pv"></span> 次
        </span>
        
      
    
  </div>


        
      </div>

      
    </div>
  </div>
</div>

</div>

  </header>

  <main>
    
      

<div class="container-fluid nopadding-x">
  <div class="row nomargin-x">
    <div class="side-col d-none d-lg-block col-lg-2">
      
  <aside class="sidebar" style="padding-left: 2rem; margin-right: -1rem">
    <div id="toc">
  <p class="toc-header">
    <i class="iconfont icon-list"></i>
    <span>目录</span>
  </p>
  <div class="toc-body" id="toc-body"></div>
</div>



  </aside>


    </div>

    <div class="col-lg-8 nopadding-x-md">
      <div class="container nopadding-x-md" id="board-ctn">
        <div id="board">
          <article class="post-content mx-auto">
            <h1 id="seo-header">项目实战：Excel批量导入中涉及的技术点</h1>
            
              <p class="note note-info">
                
                  
                    本文最后更新于：7 个月前
                  
                
              </p>
            
            
              <div class="markdown-body">
                
                <h1 id="前言"><a href="#前言" class="headerlink" title="前言"></a>前言</h1><p>Excel批量导入是软件开发中常见的需求之一。在许多业务场景下，用户需要将大量数据从Excel中，导入到系统中，以提高工作效率。</p>
<p>听起来，Excel导入，没什么技术含量。大家会说直接用阿里的<code>EasyExcel</code>，解析Excel文件，插入数据库，就完事了嘛。技术实现的大体流程确实如此。</p>
<p>不过本文的侧重点并非是使用什么三方库，来实现Excel导入的功能，而是在Excel导入过程中，有哪些技术点，以及需要注意的细节</p>
<br>

<p>□ 导入步骤：</p>
<img src="/2023/05/01/%E9%A1%B9%E7%9B%AE%E5%AE%9E%E6%88%98%EF%BC%9AExcel%E6%89%B9%E9%87%8F%E5%AF%BC%E5%85%A5%E4%B8%AD%E6%B6%89%E5%8F%8A%E7%9A%84%E6%8A%80%E6%9C%AF%E7%82%B9/Snipaste_2023-12-23_11-27-04.png" srcset="/img/loading.gif" lazyload alt="image-20230608161258537" style="zoom:80%;">  

<br>

<p>□ 在Excel批量导入过程中，主要涉及下面的技术点：</p>
<ol>
<li><p>使用什么框架来解析Excel：本文不探讨，直接选用阿里的<code>EasyExcel</code>即可</p>
</li>
<li><p>Excel文件本身的校验：主要校验文件的格式、大小</p>
</li>
<li><p>Excel内的数据校验：</p>
<figure><div class="code-wrapper"><pre class="line-numbers language-bash" data-language="bash"><code class="language-bash">是否必填：首先需要校验，字段是否有值，才能进行后面的校验
数据正确性：枚举字段、日期合法性、业务数据合法性<span class="token punctuation">(</span>例：用户id在系统中是否存在<span class="token punctuation">)</span>
特殊字符：空格、换行符、标点符号的半角-圆角
自身数据重复：Excel中，本身可能就存在重复数据，需要进行检测
与数据库已存在数据校验：<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></figure></li>
</ol>
<p>下面详细展开：</p>
<h1 id="本身校验"><a href="#本身校验" class="headerlink" title="本身校验"></a>本身校验</h1><p>1、文件格式：首先需要检测上传的文件是否是Excel文件，通常可以使用后缀名来判断</p>
<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token keyword">private</span> <span class="token keyword">boolean</span> <span class="token function">isExcelFile</span><span class="token punctuation">(</span><span class="token class-name">String</span> fileName<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
    <span class="token keyword">return</span> fileName<span class="token punctuation">.</span><span class="token function">toLowerCase</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">endsWith</span><span class="token punctuation">(</span><span class="token string">".xls"</span><span class="token punctuation">)</span> 
        <span class="token operator">||</span> fileName<span class="token punctuation">.</span><span class="token function">toLowerCase</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">endsWith</span><span class="token punctuation">(</span><span class="token string">".xlsx"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div></figure>

<p>2、文件大小：上传是1个比较耗费资源的操作，即使产品需求层面未限制文件大小、数据条数，也需要做兜底的控制</p>
<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token comment">// 出于系统保护,本场景1M已经有3万+数据量了 </span>
<span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token keyword">int</span> <span class="token constant">EXCEL_SIZE_LIMIT</span> <span class="token operator">=</span> <span class="token number">1024</span><span class="token punctuation">;</span> 
<span class="token keyword">private</span> <span class="token keyword">void</span> <span class="token function">checkExcelSize</span><span class="token punctuation">(</span><span class="token class-name">MultipartFile</span> file<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
    <span class="token class-name">AssertUtil</span><span class="token punctuation">.</span><span class="token function">notNull</span><span class="token punctuation">(</span>file<span class="token punctuation">,</span> <span class="token string">"数据导入,上传文件为空"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

    <span class="token keyword">long</span> fileKb <span class="token operator">=</span> file<span class="token punctuation">.</span><span class="token function">getSize</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">/</span> <span class="token number">1024</span><span class="token punctuation">;</span> 
    <span class="token class-name">AssertUtil</span><span class="token punctuation">.</span><span class="token function">isTrue</span><span class="token punctuation">(</span>fileKb <span class="token operator">&lt;=</span> <span class="token number">1024</span><span class="token punctuation">,</span> <span class="token string">"数据导入,仅支持1M以下的Excel文件"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></figure>



<h1 id="必填校验"><a href="#必填校验" class="headerlink" title="必填校验"></a>必填校验</h1><p>根据配置(数据库、配置文件)，判断某些字段是否必填，如果是必填，但值为空，将不允许上传</p>
<p>具体判断有以下两种方式：</p>
<p>1、直接if-else：</p>
<p>优点：最简单、直观</p>
<p>缺点：扩展性差，如果后续迭代，要增加导入的字段、修改字段名，将修改校验代码，不符合开闭原则</p>
<img src="/2023/05/01/%E9%A1%B9%E7%9B%AE%E5%AE%9E%E6%88%98%EF%BC%9AExcel%E6%89%B9%E9%87%8F%E5%AF%BC%E5%85%A5%E4%B8%AD%E6%B6%89%E5%8F%8A%E7%9A%84%E6%8A%80%E6%9C%AF%E7%82%B9/Snipaste_2023-12-12_14-01-47.png" srcset="/img/loading.gif" lazyload alt="image-20230608161258537" style="zoom:80%;"> 

<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token annotation punctuation">@Data</span>
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">ExcelDto</span> <span class="token punctuation">&#123;</span>

    <span class="token annotation punctuation">@ExcelProperty</span><span class="token punctuation">(</span>value <span class="token operator">=</span> <span class="token string">"用户名"</span><span class="token punctuation">,</span> index <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">)</span>
    <span class="token keyword">private</span> <span class="token class-name">String</span> name<span class="token punctuation">;</span>

    <span class="token annotation punctuation">@ExcelProperty</span><span class="token punctuation">(</span>value <span class="token operator">=</span> <span class="token string">"性别"</span><span class="token punctuation">,</span> index <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">)</span>
    <span class="token keyword">private</span> <span class="token class-name">String</span> sex<span class="token punctuation">;</span>

    <span class="token annotation punctuation">@ExcelProperty</span><span class="token punctuation">(</span>value <span class="token operator">=</span> <span class="token string">"家庭住址"</span><span class="token punctuation">,</span> index <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">)</span>
    <span class="token keyword">private</span> <span class="token class-name">String</span> address<span class="token punctuation">;</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></figure>

<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token comment">/**
 * 检查字段必填
 * @param excelDtoList
 * @param mustFillFields  必填的字段：可以从数据库、配置中加载
 */</span>
<span class="token keyword">public</span> <span class="token function">checkMustFill</span><span class="token punctuation">(</span><span class="token class-name">List</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">ExcelDto</span><span class="token punctuation">></span></span> excelDtoList<span class="token punctuation">,</span>
                     <span class="token class-name">Set</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">String</span><span class="token punctuation">></span></span> mustFillFields<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
    <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token class-name">ExcelDto</span> excelDto <span class="token operator">:</span> excelDtoList<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        <span class="token keyword">if</span><span class="token punctuation">(</span>mustFillFields<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span><span class="token string">"name"</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        	<span class="token class-name">AssertUtil</span><span class="token punctuation">.</span><span class="token function">notBlank</span><span class="token punctuation">(</span>execlDto<span class="token punctuation">.</span><span class="token function">getName</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">"name不能为空"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">&#125;</span>
        <span class="token keyword">if</span><span class="token punctuation">(</span>mustFillFields<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span><span class="token string">"sex"</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        	<span class="token class-name">AssertUtil</span><span class="token punctuation">.</span><span class="token function">notBlank</span><span class="token punctuation">(</span>execlDto<span class="token punctuation">.</span><span class="token function">getSex</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">"sex不能为空"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">&#125;</span>
        <span class="token comment">// ...其他字段</span>
    <span class="token punctuation">&#125;</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></figure>

<p>可以看到，这个代码是非常不优雅的，而且如果以后别人维护的时候不知道，把某个字段重命名了，那直接就炸了</p>
<p>2、反射：使用反射获取字段列表，进行判断</p>
<p>优点：如果增加了必填字段，仅需修改配置，校验逻辑无需修改</p>
<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token comment">/**
 * 检查字段必填
 * @param excelDtoList
 * @param mustFillFields  必填的字段：可以从数据库、配置中加载
 */</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token function">checkMustFill</span><span class="token punctuation">(</span><span class="token class-name">List</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">ExcelDto</span><span class="token punctuation">></span></span> excelDtoList<span class="token punctuation">,</span>
                          <span class="token class-name">Set</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">String</span><span class="token punctuation">></span></span> mustFillFields<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
    <span class="token class-name">Field</span><span class="token punctuation">[</span><span class="token punctuation">]</span> fields <span class="token operator">=</span> datum<span class="token punctuation">.</span><span class="token function">getClass</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">getDeclaredFields</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token class-name">StringBuilder</span> response <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">StringBuilder</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token class-name">Field</span> field <span class="token operator">:</span> fields<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token operator">!</span>field<span class="token punctuation">.</span><span class="token function">isAnnotationPresent</span><span class="token punctuation">(</span><span class="token class-name">ExcelProperty</span><span class="token punctuation">.</span><span class="token keyword">class</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
            <span class="token keyword">continue</span><span class="token punctuation">;</span>
        <span class="token punctuation">&#125;</span>
        <span class="token comment">// 判断是否是必填字段</span>
        <span class="token class-name">String</span> fieldName <span class="token operator">=</span> field<span class="token punctuation">.</span><span class="token function">getName</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token operator">!</span>mustFillFields<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>fieldName<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span> 
            <span class="token keyword">continue</span><span class="token punctuation">;</span>
        <span class="token punctuation">&#125;</span>

        field<span class="token punctuation">.</span><span class="token function">setAccessible</span><span class="token punctuation">(</span><span class="token boolean">true</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token class-name">String</span> <span class="token class-name">FieldValue</span> <span class="token operator">=</span> field<span class="token punctuation">.</span><span class="token function">get</span><span class="token punctuation">(</span>datum<span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">toString</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token class-name">AssertUtil</span><span class="token punctuation">.</span><span class="token function">notBlank</span><span class="token punctuation">(</span><span class="token class-name">FieldValue</span><span class="token punctuation">,</span> fieldName <span class="token operator">+</span> <span class="token string">"不能为空"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></figure>







<h1 id="数据正确性校验"><a href="#数据正确性校验" class="headerlink" title="数据正确性校验"></a>数据正确性校验</h1><ul>
<li><p>枚举：这个就不必详谈，根据各自的系统枚举，进行校验即可</p>
<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token keyword">boolean</span> typeLegal <span class="token operator">=</span> <span class="token class-name">ActivityTypeEnum</span><span class="token punctuation">.</span><span class="token function">isBusinessGroup</span><span class="token punctuation">(</span>type<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token class-name">AssertUtil</span><span class="token punctuation">.</span><span class="token function">isTrue</span><span class="token punctuation">(</span>b<span class="token punctuation">,</span> <span class="token string">"活动线id非B端,活动线id："</span> <span class="token operator">+</span> type<span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div></figure>
</li>
<li><p>时间校验：这里提一下，除了时间格式；如果涉及生效时间、失效时间，需要校验【生效时间&lt;&#x3D;失效时间】</p>
<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token keyword">boolean</span> dataLegal <span class="token operator">=</span> effectiveTime<span class="token punctuation">.</span><span class="token function">before</span><span class="token punctuation">(</span>expireTime<span class="token punctuation">)</span>
<span class="token class-name">AssertUtil</span><span class="token punctuation">.</span><span class="token function">isTrue</span><span class="token punctuation">(</span>dataLegal<span class="token punctuation">,</span> <span class="token string">"生效时间>=失效时间)"</span><span class="token punctuation">,</span> excelLineIndex<span class="token punctuation">)</span><span class="token punctuation">;</span>    <span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div></figure>
</li>
<li><p>业务数据正确性：</p>
<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token class-name">AssertUtil</span><span class="token punctuation">.</span><span class="token function">notNull</span><span class="token punctuation">(</span><span class="token function">getUserById</span><span class="token punctuation">(</span>userId<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">"用户id不存在"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>  <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div></figure></li>
</ul>
<h1 id="特殊字符处理"><a href="#特殊字符处理" class="headerlink" title="特殊字符处理"></a>特殊字符处理</h1><p>常见的特殊字符有：空格、换行符、Tab制表符、标点符号的半角-圆角转换</p>
<p>思路：同样是利用发射，获取所有属性值，进行特殊字符判断，并处理</p>
<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token comment">// 逗号</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errorComma <span class="token operator">=</span> <span class="token string">"，"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> correctComma <span class="token operator">=</span> <span class="token string">","</span><span class="token punctuation">;</span>

<span class="token comment">// 句号</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errorPeriod <span class="token operator">=</span> <span class="token string">"。"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> correctPeriod <span class="token operator">=</span> <span class="token string">"."</span><span class="token punctuation">;</span>

<span class="token comment">// 冒号</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errorSemicolon <span class="token operator">=</span> <span class="token string">"："</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> correctSemicolon <span class="token operator">=</span> <span class="token string">":"</span><span class="token punctuation">;</span>

<span class="token comment">// 双引号</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errorDoubleQuotesRight <span class="token operator">=</span> <span class="token string">"”"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errorDoubleQuotesLeft <span class="token operator">=</span> <span class="token string">"“"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> correctDoubleQuotes <span class="token operator">=</span> <span class="token string">"\""</span><span class="token punctuation">;</span>

<span class="token comment">// 单引号</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errorSingleQuotesRight <span class="token operator">=</span> <span class="token string">"’"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errorSingleQuotesLeft <span class="token operator">=</span> <span class="token string">"‘"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> correctSingleQuotes <span class="token operator">=</span> <span class="token string">"\""</span><span class="token punctuation">;</span>

<span class="token comment">// 括号</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errorBracketLeft <span class="token operator">=</span> <span class="token string">"（"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errorBracketRight <span class="token operator">=</span> <span class="token string">"）"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> correctBracketRight <span class="token operator">=</span> <span class="token string">")"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> correctBracketLeft <span class="token operator">=</span> <span class="token string">"("</span><span class="token punctuation">;</span>

<span class="token comment">// 问号</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errQuestionMark <span class="token operator">=</span> <span class="token string">"？"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> correctQuestionMark <span class="token operator">=</span> <span class="token string">"?"</span><span class="token punctuation">;</span>

<span class="token comment">// 感叹号</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> errExclamationMark <span class="token operator">=</span> <span class="token string">"！"</span><span class="token punctuation">;</span>
<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token class-name">String</span> correctExclamationMark <span class="token operator">=</span> <span class="token string">"!"</span><span class="token punctuation">;</span>

<span class="token keyword">private</span> <span class="token keyword">void</span> <span class="token function">replaceSymbol</span><span class="token punctuation">(</span><span class="token class-name">LabelCopyDTO</span> labelCopy<span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">IllegalAccessException</span> <span class="token punctuation">&#123;</span>
    <span class="token class-name">Field</span><span class="token punctuation">[</span><span class="token punctuation">]</span> declaredFields <span class="token operator">=</span> labelCopy<span class="token punctuation">.</span><span class="token function">getClass</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">getDeclaredFields</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token class-name">Field</span> field <span class="token operator">:</span> declaredFields<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        field<span class="token punctuation">.</span><span class="token function">setAccessible</span><span class="token punctuation">(</span><span class="token boolean">true</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">if</span> <span class="token punctuation">(</span>field<span class="token punctuation">.</span><span class="token function">get</span><span class="token punctuation">(</span>labelCopy<span class="token punctuation">)</span> <span class="token keyword">instanceof</span> <span class="token class-name">String</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
            <span class="token class-name">String</span> value <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token class-name">String</span><span class="token punctuation">)</span> field<span class="token punctuation">.</span><span class="token function">get</span><span class="token punctuation">(</span>labelCopy<span class="token punctuation">)</span><span class="token punctuation">;</span>
            <span class="token class-name">String</span> newValue <span class="token operator">=</span> <span class="token keyword">this</span><span class="token punctuation">.</span><span class="token function">replaceSymbol</span><span class="token punctuation">(</span>value<span class="token punctuation">)</span><span class="token punctuation">;</span>
            field<span class="token punctuation">.</span><span class="token function">set</span><span class="token punctuation">(</span>labelCopy<span class="token punctuation">,</span> newValue<span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token punctuation">&#125;</span>
    <span class="token punctuation">&#125;</span>
<span class="token punctuation">&#125;</span>

<span class="token keyword">private</span> <span class="token class-name">String</span> <span class="token function">replaceSymbol</span><span class="token punctuation">(</span><span class="token class-name">String</span> value<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
    <span class="token comment">// 换行</span>
    value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">trim</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
        <span class="token punctuation">.</span><span class="token function">replaceAll</span><span class="token punctuation">(</span><span class="token string">"\r\n"</span><span class="token punctuation">,</span> <span class="token string">""</span><span class="token punctuation">)</span> <span class="token comment">// window系统 换行</span>
        <span class="token punctuation">.</span><span class="token function">replaceAll</span><span class="token punctuation">(</span><span class="token string">"\r"</span><span class="token punctuation">,</span> <span class="token string">""</span><span class="token punctuation">)</span> <span class="token comment">// mac系统 换行</span>
        <span class="token punctuation">.</span><span class="token function">replaceAll</span><span class="token punctuation">(</span><span class="token string">"\n"</span><span class="token punctuation">,</span> <span class="token string">""</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment">// unix系统 换行</span>

    <span class="token comment">// Tab</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">startsWith</span><span class="token punctuation">(</span><span class="token string">"\t"</span><span class="token punctuation">)</span> <span class="token operator">||</span> value<span class="token punctuation">.</span><span class="token function">endsWith</span><span class="token punctuation">(</span><span class="token string">"\t"</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replaceAll</span><span class="token punctuation">(</span><span class="token string">"\t"</span><span class="token punctuation">,</span> <span class="token string">""</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>

    <span class="token comment">// 特殊空格,mysql ——> char(194)</span>
    <span class="token comment">// 如果在开头和末尾，则去掉</span>
    <span class="token comment">// 如果在中间，则替换为普通空格</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span><span class="token string">"\u00A0"</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span><span class="token string">"\u00A0"</span><span class="token punctuation">,</span> <span class="token string">" "</span><span class="token punctuation">)</span>
            <span class="token punctuation">.</span><span class="token function">trim</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>

    <span class="token comment">// 逗号</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errorComma<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errorComma<span class="token punctuation">,</span> correctComma<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token comment">// 句号</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errorPeriod<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errorPeriod<span class="token punctuation">,</span> correctPeriod<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token comment">// 冒号</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errorSemicolon<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errorSemicolon<span class="token punctuation">,</span> correctSemicolon<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token comment">// 双引号</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errorDoubleQuotesLeft<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errorDoubleQuotesLeft<span class="token punctuation">,</span> correctDoubleQuotes<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errorDoubleQuotesRight<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errorDoubleQuotesRight<span class="token punctuation">,</span> correctDoubleQuotes<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token comment">// 单引号</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errorSingleQuotesLeft<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errorSingleQuotesLeft<span class="token punctuation">,</span> correctSingleQuotes<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errorSingleQuotesRight<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errorSingleQuotesRight<span class="token punctuation">,</span> correctSingleQuotes<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token comment">// 括号</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errorBracketLeft<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errorBracketLeft<span class="token punctuation">,</span> correctBracketLeft<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errorBracketRight<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errorBracketRight<span class="token punctuation">,</span> correctBracketRight<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token comment">// 问号</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errQuestionMark<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errQuestionMark<span class="token punctuation">,</span> correctQuestionMark<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token comment">// 感叹号</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span>value<span class="token punctuation">.</span><span class="token function">contains</span><span class="token punctuation">(</span>errExclamationMark<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        value <span class="token operator">=</span> value<span class="token punctuation">.</span><span class="token function">replace</span><span class="token punctuation">(</span>errExclamationMark<span class="token punctuation">,</span> correctExclamationMark<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
    <span class="token keyword">return</span> value<span class="token punctuation">;</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></figure>





<h1 id="自身数据重复校验"><a href="#自身数据重复校验" class="headerlink" title="自身数据重复校验"></a>自身数据重复校验</h1><p>可以利用Map，逐行判断，若数据在Map中已存在，则说明重复</p>
<p>注意点：RepeatKey，需要自行定义，哪些字段相同，才算是重复的数据</p>
<img src="/2023/05/01/%E9%A1%B9%E7%9B%AE%E5%AE%9E%E6%88%98%EF%BC%9AExcel%E6%89%B9%E9%87%8F%E5%AF%BC%E5%85%A5%E4%B8%AD%E6%B6%89%E5%8F%8A%E7%9A%84%E6%8A%80%E6%9C%AF%E7%82%B9/Snipaste_2023-12-12_14-01-18.png" srcset="/img/loading.gif" lazyload alt="image-20230608161258537" style="zoom:80%;">  

<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token annotation punctuation">@Data</span>
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">ExcelDto</span> <span class="token punctuation">&#123;</span>

    <span class="token annotation punctuation">@ExcelProperty</span><span class="token punctuation">(</span>value <span class="token operator">=</span> <span class="token string">"用户名"</span><span class="token punctuation">,</span> index <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">)</span>
    <span class="token keyword">private</span> <span class="token class-name">String</span> name<span class="token punctuation">;</span>

    <span class="token annotation punctuation">@ExcelProperty</span><span class="token punctuation">(</span>value <span class="token operator">=</span> <span class="token string">"性别"</span><span class="token punctuation">,</span> index <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">)</span>
    <span class="token keyword">private</span> <span class="token class-name">String</span> sex<span class="token punctuation">;</span>

    <span class="token annotation punctuation">@ExcelProperty</span><span class="token punctuation">(</span>value <span class="token operator">=</span> <span class="token string">"家庭住址"</span><span class="token punctuation">,</span> index <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">)</span>
    <span class="token keyword">private</span> <span class="token class-name">String</span> address<span class="token punctuation">;</span>
    
    <span class="token keyword">public</span> <span class="token class-name">String</span> <span class="token function">buildRepeatCheckKey</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        <span class="token keyword">return</span> <span class="token class-name">String</span><span class="token punctuation">.</span><span class="token function">format</span><span class="token punctuation">(</span><span class="token string">"%s_%s_%s"</span><span class="token punctuation">,</span>name<span class="token punctuation">,</span> sex<span class="token punctuation">,</span> awardId<span class="token punctuation">,</span> address<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>    
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></figure>

<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token keyword">private</span> <span class="token keyword">void</span> <span class="token function">checkExcelRepeat</span><span class="token punctuation">(</span><span class="token class-name">List</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">ExcelDto</span><span class="token punctuation">></span></span> excelDtoList<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
    <span class="token class-name">Map</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">String</span><span class="token punctuation">,</span> <span class="token class-name">Integer</span><span class="token punctuation">></span></span> elementMap <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">HashMap</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token punctuation">></span></span><span class="token punctuation">(</span>excelDtoList<span class="token punctuation">.</span><span class="token function">size</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">int</span> excelLineIndex <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
    <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token class-name">ExcelDto</span> excelDto <span class="token operator">:</span> excelDtoList<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        excelLineIndex<span class="token operator">++</span><span class="token punctuation">;</span>
        <span class="token function">checkExcelRepeat</span><span class="token punctuation">(</span>excelDto<span class="token punctuation">,</span> elementMap<span class="token punctuation">,</span> excelLineIndex<span class="token punctuation">)</span>
    <span class="token punctuation">&#125;</span>
<span class="token punctuation">&#125;</span>

<span class="token keyword">private</span> <span class="token keyword">void</span> <span class="token function">checkExcelRepeat</span><span class="token punctuation">(</span><span class="token class-name">ExcelDto</span> data<span class="token punctuation">,</span> 
                              <span class="token class-name">Map</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">String</span><span class="token punctuation">,</span> <span class="token class-name">Integer</span><span class="token punctuation">></span></span> elementMap<span class="token punctuation">,</span>
                              <span class="token keyword">int</span> lineIndex<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
    <span class="token class-name">String</span> key <span class="token operator">=</span> data<span class="token punctuation">.</span><span class="token function">buildRepeatCheckKey</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token operator">!</span>elementMap<span class="token punctuation">.</span><span class="token function">containsKey</span><span class="token punctuation">(</span>key<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
        elementMap<span class="token punctuation">.</span><span class="token function">put</span><span class="token punctuation">(</span>key<span class="token punctuation">,</span> lineIndex<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span> <span class="token keyword">else</span> <span class="token punctuation">&#123;</span>
        <span class="token class-name">String</span> repateFormat <span class="token operator">=</span> <span class="token string">"第%s行与第%s行重复"</span>
        <span class="token class-name">String</span> errorMsg <span class="token operator">=</span> <span class="token class-name">String</span><span class="token punctuation">.</span><span class="token function">format</span><span class="token punctuation">(</span>repateFormat<span class="token punctuation">,</span> 
                                        lineIndex<span class="token punctuation">,</span> 
                                        elementMap<span class="token punctuation">.</span><span class="token function">get</span><span class="token punctuation">(</span>key<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
        <span class="token keyword">throw</span> <span class="token keyword">new</span> <span class="token class-name">BizException</span><span class="token punctuation">(</span>errorMsg<span class="token punctuation">)</span><span class="token punctuation">;</span>
    <span class="token punctuation">&#125;</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></figure>





<h1 id="数据已存在校验"><a href="#数据已存在校验" class="headerlink" title="数据已存在校验"></a>数据已存在校验</h1><p>这个，也不展开说，根据各自的业务，针对Excel导入的数据，与系统中已存在的数据，进行校验即可</p>
<figure><div class="code-wrapper"><pre class="line-numbers language-java" data-language="java"><code class="language-java"><span class="token comment">// 伪代码</span>
<span class="token keyword">public</span> <span class="token function">checkDbExistData</span><span class="token punctuation">(</span><span class="token class-name">List</span><span class="token generics"><span class="token punctuation">&lt;</span><span class="token class-name">ExcelDto</span><span class="token punctuation">></span></span> excelDtoList<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
    <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token class-name">ExcelDto</span> excelDto <span class="token operator">:</span> excelDtoList<span class="token punctuation">)</span> <span class="token punctuation">&#123;</span>
       <span class="token class-name">DataDto</span> existData <span class="token operator">=</span> <span class="token function">getDataFromDByCondition</span><span class="token punctuation">(</span>excelDto<span class="token punctuation">)</span><span class="token punctuation">;</span>
       <span class="token class-name">AssertUtil</span><span class="token punctuation">.</span><span class="token function">isNull</span><span class="token punctuation">(</span>dbExistData<span class="token punctuation">,</span> <span class="token string">"该数据,系统中已存在"</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
       log<span class="token punctuation">.</span><span class="token function">warn</span><span class="token punctuation">(</span><span class="token string">"该数据在系统中已存在,existDataId：&#123;&#125;"</span><span class="token punctuation">,</span> existData<span class="token punctuation">.</span><span class="token function">getId</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
    <span class="token punctuation">&#125;</span>
<span class="token punctuation">&#125;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div></figure>




                
              </div>
            
            <hr/>
            <div>
              <div class="post-metas my-3">
  
    <div class="post-meta mr-3 d-flex align-items-center">
      <i class="iconfont icon-category"></i>
      

<span class="category-chains">
  
  
    
      <span class="category-chain">
        
  <a href="/categories/%E9%A1%B9%E7%9B%AE%E5%AE%9E%E6%88%98/" class="category-chain-item">项目实战</a>
  
  

      </span>
    
  
</span>

    </div>
  
  
    <div class="post-meta">
      <i class="iconfont icon-tags"></i>
      
        <a href="/tags/Excel/" class="print-no-link">#Excel</a>
      
        <a href="/tags/%E5%8F%8D%E5%B0%84/" class="print-no-link">#反射</a>
      
    </div>
  
</div>


              
  

  <div class="license-box my-3">
    <div class="license-title">
      <div>项目实战：Excel批量导入中涉及的技术点</div>
      <div>http://example.com/2023/05/01/项目实战：Excel批量导入中涉及的技术点/</div>
    </div>
    <div class="license-meta">
      
        <div class="license-meta-item">
          <div>作者</div>
          <div>XiaoMaDing</div>
        </div>
      
      
        <div class="license-meta-item license-meta-date">
          <div>发布于</div>
          <div>2023年5月1日</div>
        </div>
      
      
      
        <div class="license-meta-item">
          <div>许可协议</div>
          <div>
            
              
              
                <a class="print-no-link" target="_blank" href="https://creativecommons.org/licenses/by/4.0/">
                  <span class="hint--top hint--rounded" aria-label="BY - 署名">
                    <i class="iconfont icon-by"></i>
                  </span>
                </a>
              
            
          </div>
        </div>
      
    </div>
    <div class="license-icon iconfont"></div>
  </div>



              
                <div class="post-prevnext my-3">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/2023/05/22/%E9%A1%B9%E7%9B%AE%E5%AE%9E%E6%88%98%EF%BC%9A%E7%BA%BF%E7%A8%8B%E6%B1%A0%E5%8C%85%E8%A3%85%E4%B8%8E%E5%8F%82%E6%95%B0%E4%BC%A0%E9%80%92/" title="项目实战：线程池包装与参数传递">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">项目实战：线程池包装与参数传递</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/2023/04/15/%E9%A1%B9%E7%9B%AE%E5%AE%9E%E6%88%98%EF%BC%9A%E6%8E%A5%E5%8F%A3%E5%B9%B6%E5%8F%91%E6%8E%A7%E5%88%B6/" title="项目实战：接口并发控制">
                        <span class="hidden-mobile">项目实战：接口并发控制</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
  
  
    <article id="comments" lazyload>
      
  <div id="valine"></div>
  <script type="text/javascript">
    Fluid.utils.loadComments('#valine', function() {
      Fluid.utils.createScript('https://lib.baomitu.com/valine/1.5.1/Valine.min.js', function() {
        var options = Object.assign(
          {"appId":"fwhgdRnxBMBQuwXd6G2KY7uA-gzGzoHsz","appKey":"wz9qgHwvuMw8Y0yXAvL1KEr4","path":"window.location.pathname","placeholder":null,"avatar":"retro","meta":["nick","mail","link"],"requiredFields":[],"pageSize":10,"lang":"zh-CN","highlight":false,"recordIP":false,"serverURLs":"","emojiCDN":null,"emojiMaps":null,"enableQQ":false},
          {
            el: "#valine",
            path: window.location.pathname
          }
        )
        new Valine(options);
        Fluid.utils.waitElementVisible('#valine .vcontent', () => {
          var imgSelector = '#valine .vcontent img:not(.vemoji)';
          Fluid.plugins.imageCaption(imgSelector);
          Fluid.plugins.fancyBox(imgSelector);
        })
      });
    });
  </script>
  <noscript>Please enable JavaScript to view the comments</noscript>


    </article>
  


          </article>
        </div>
      </div>
    </div>

    <div class="side-col d-none d-lg-block col-lg-2">
      

    </div>
  </div>
</div>





  



  



  



  



  







    

    
      <a id="scroll-top-button" aria-label="TOP" href="#" role="button">
        <i class="iconfont icon-arrowup" aria-hidden="true"></i>
      </a>
    

    
      <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v" for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>

    

    
  </main>

  <footer>
    <div class="footer-inner">
  
    <div class="footer-content">
       <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a> <i class="iconfont icon-love"></i> <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener"><span>Fluid</span></a> 
    </div>
  
  
    <div class="statistics">
  
  

  
    
      <span id="busuanzi_container_site_pv" style="display: none">
        总访问量 
        <span id="busuanzi_value_site_pv"></span>
         次
      </span>
    
    
      <span id="busuanzi_container_site_uv" style="display: none">
        总访客数 
        <span id="busuanzi_value_site_uv"></span>
         人
      </span>
    
    
  
</div>

  
  
  
</div>

  </footer>

  <!-- Scripts -->
  
  <script  src="https://lib.baomitu.com/nprogress/0.2.0/nprogress.min.js" ></script>
  <link  rel="stylesheet" href="https://lib.baomitu.com/nprogress/0.2.0/nprogress.min.css" />

  <script>
    NProgress.configure({"showSpinner":false,"trickleSpeed":100})
    NProgress.start()
    window.addEventListener('load', function() {
      NProgress.done();
    })
  </script>


<script  src="https://lib.baomitu.com/jquery/3.6.0/jquery.min.js" ></script>
<script  src="https://lib.baomitu.com/twitter-bootstrap/4.6.1/js/bootstrap.min.js" ></script>
<script  src="/js/events.js" ></script>
<script  src="/js/plugins.js" ></script>


  <script  src="https://lib.baomitu.com/typed.js/2.0.12/typed.min.js" ></script>
  <script>
    (function (window, document) {
      var typing = Fluid.plugins.typing;
      var subtitle = document.getElementById('subtitle');
      if (!subtitle || !typing) {
        return;
      }
      var text = subtitle.getAttribute('data-typed-text');
      
        typing(text);
      
    })(window, document);
  </script>




  
    <script  src="/js/img-lazyload.js" ></script>
  




  
<script>
  Fluid.utils.createScript('https://lib.baomitu.com/tocbot/4.18.2/tocbot.min.js', function() {
    var toc = jQuery('#toc');
    if (toc.length === 0 || !window.tocbot) { return; }
    var boardCtn = jQuery('#board-ctn');
    var boardTop = boardCtn.offset().top;

    window.tocbot.init(Object.assign({
      tocSelector     : '#toc-body',
      contentSelector : '.markdown-body',
      linkClass       : 'tocbot-link',
      activeLinkClass : 'tocbot-active-link',
      listClass       : 'tocbot-list',
      isCollapsedClass: 'tocbot-is-collapsed',
      collapsibleClass: 'tocbot-is-collapsible',
      scrollSmooth    : true,
      includeTitleTags: true,
      headingsOffset  : -boardTop,
    }, CONFIG.toc));
    if (toc.find('.toc-list-item').length > 0) {
      toc.css('visibility', 'visible');
    }

    Fluid.events.registerRefreshCallback(function() {
      if ('tocbot' in window) {
        tocbot.refresh();
        var toc = jQuery('#toc');
        if (toc.length === 0 || !tocbot) {
          return;
        }
        if (toc.find('.toc-list-item').length > 0) {
          toc.css('visibility', 'visible');
        }
      }
    });
  });
</script>


  <script  src="https://lib.baomitu.com/prism/1.29.0/plugins/line-numbers/prism-line-numbers.min.js" ></script>

  <script src=https://lib.baomitu.com/clipboard.js/2.0.11/clipboard.min.js></script>

  <script>Fluid.plugins.codeWidget();</script>


  
<script>
  Fluid.utils.createScript('https://lib.baomitu.com/anchor-js/4.3.1/anchor.min.js', function() {
    window.anchors.options = {
      placement: CONFIG.anchorjs.placement,
      visible  : CONFIG.anchorjs.visible
    };
    if (CONFIG.anchorjs.icon) {
      window.anchors.options.icon = CONFIG.anchorjs.icon;
    }
    var el = (CONFIG.anchorjs.element || 'h1,h2,h3,h4,h5,h6').split(',');
    var res = [];
    for (var item of el) {
      res.push('.markdown-body > ' + item.trim());
    }
    if (CONFIG.anchorjs.placement === 'left') {
      window.anchors.options.class = 'anchorjs-link-left';
    }
    window.anchors.add(res.join(', '));

    Fluid.events.registerRefreshCallback(function() {
      if ('anchors' in window) {
        anchors.removeAll();
        var el = (CONFIG.anchorjs.element || 'h1,h2,h3,h4,h5,h6').split(',');
        var res = [];
        for (var item of el) {
          res.push('.markdown-body > ' + item.trim());
        }
        if (CONFIG.anchorjs.placement === 'left') {
          anchors.options.class = 'anchorjs-link-left';
        }
        anchors.add(res.join(', '));
      }
    });
  });
</script>


  
<script>
  Fluid.utils.createScript('https://lib.baomitu.com/fancybox/3.5.7/jquery.fancybox.min.js', function() {
    Fluid.plugins.fancyBox();
  });
</script>


  <script>Fluid.plugins.imageCaption();</script>

  <script  src="/js/local-search.js" ></script>

  <script defer src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" ></script>





<!-- 主题的启动项，将它保持在最底部 -->
<!-- the boot of the theme, keep it at the bottom -->
<script  src="/js/boot.js" ></script>


  

  <noscript>
    <div class="noscript-warning">博客在允许 JavaScript 运行的环境下浏览效果更佳</div>
  </noscript>
</body>
</html>
